*-----------------------------
*CLEAN DATA ON MAYOR ELECTIONS
*-----------------------------
use "$data\mayorelections.dta"
rename gkz code
drop kandidat* traeger_kand* stimmen_kand* stimmen_* wahlberechtigte wahlbeteiligung whler ungueltig_stimmen ungueltige_prct gueltige_stimmen

*GENERATE AN INDICATOR FOR RUNOFF ELECTIONS
gen stichwahlnum=0
replace stichwahlnum=1 if stichwahl=="Stichwahl"
bysort code wahlperiode: egen mean_stichwahl=mean(stichwahlnum)

*DROP THE FIRST ELECTION WHERE A RUNOFF ELECTION TOOK PLACE
drop if  mean_stichwahl>0 & stichwahlnum==0
drop mean_stichwahl stichwahlnum

*Drop first runoff when there have been two runoffs
drop if code==432001 & wahltag=="3/25/2007"
drop if code==432002 & wahltag=="4/28/2002"
drop if code==433001 & wahltag=="5/16/1993"
drop if code==440002 & wahltag=="5/2/1999"
drop if code==633029 & wahltag=="9/22/2002"

*INDEX FOR DIRECT ELECTIONS
gen bmwahl = 1

*extract year from election date variable
gen year=regexs(0) if (regexm(wahltag, "[0-9][0-9][0-9][0-9]$")) 
destring year, replace

*CORRECT WRONG NUMBERING OF ELECTORAL PERIODS BY STATISTICAL OFFICE OF HESSE
by code: replace wahlperiode=3 if wahlperiode[_n-1]==2
by code: replace wahlperiode=4 if wahlperiode[_n-1]==3
by code: replace wahlperiode=5 if wahlperiode[_n-1]==4

*recode variable on party affiliation of mayor
gen traeger_gewaehlt_temp=traeger_gewaehlt
replace traeger_gewaehlt_temp="None" if traeger_gewaehlt==""
sort code year

save mayorelections_v2, replace
clear


*--------------------------------------------------------
*CREATE AUXILIARY FILE THAT INDICATES REFORM COHORTS
*--------------------------------------------------------
use "$data\mayorelections_v2.dta"

*keep one observation per municipality and only keep regular municipalities
by code, sort: keep if _n == 1
gen code3dig = mod(code,1000)
drop if code3dig == 0

*define cohort dummies
gen first_direlect = 1
gen first1993 = 1 if year == 1993
gen first1994 = 1 if year == 1994
gen first1995 = 1 if year == 1995
gen first1996 = 1 if year == 1996
gen first1997 = 1 if year == 1997
gen first1998 = 1 if year == 1998
replace first1993 = 0 if first1993 ==.
replace first1994 = 0 if first1994 ==.
replace first1995 = 0 if first1995 ==.
replace first1996 = 0 if first1996 ==.
replace first1997 = 0 if first1997 ==.
replace first1998 = 0 if first1998 ==.

keep code year bmwahl-first1998
sort code year

save firstdirektwahlen, replace


clear
*--------------------------------------------------------
*MERGE MUNICIPAL REVENUE DATA WITH CLEANED ELECTION DATA
*--------------------------------------------------------
use "$data\grants.dta" 
sort code year

merge code year using "$data\mayorelections_v2.dta"
drop _merge
sort code year

*-----------------------------------------------------
*MERGE WITH DATA THAT INDICATES FIRST DIRECT ELECTIONS
*-----------------------------------------------------
merge code year using "$data\firstdirektwahlen.dta"
drop if Reg_Gem ==.
sort code year
drop _merge

*----------------------------------------------------------------------------
*MERGE WITH DATA ON OTHER FISCAL AND DEMOGRAPHIC VARIABLES
*----------------------------------------------------------------------------
merge code year using "$data\controls.dta"
drop if Reg_Gem ==.
drop _merge
sort code year

*COPY DOWN INFORMATION ON municipality area 
by code: replace flaeche = flaeche[_n+1] if flaeche ==.
by code: replace flaeche = flaeche[_n+1] if flaeche ==.
by code: replace flaeche = flaeche[_n+1] if flaeche ==.
by code: replace flaeche = flaeche[_n+1] if flaeche ==.
by code: replace flaeche = flaeche[_n+1] if flaeche ==.
by code: replace flaeche = flaeche[_n+1] if flaeche ==.
by code: replace flaeche = flaeche[_n+1] if flaeche ==.
by code: replace flaeche = flaeche[_n+1] if flaeche ==.
by code: replace flaeche = flaeche[_n+1] if flaeche ==.
by code: replace flaeche = flaeche[_n+1] if flaeche ==.
sort code year

*COPY DOWN INFORMATION ON ELECTORAL PERIOD 
by code: replace wahlperiode=wahlperiode[_n-1] if wahlperiode==.
replace wahlperiode = wahlperiode - 1 if code == 433001
replace wahlperiode = wahlperiode - 1 if code == 432002 & wahlperiode > 1
replace wahlperiode = wahlperiode - 1 if code == 440002 & wahlperiode > 1
replace wahlperiode = wahlperiode - 1 if code == 633029 & wahlperiode > 1

*GENERATE INDICATOR FOR DIRECT ELECTIONS AND INDIRECT ELECTIONS
gen direct_elect = 0 if wahlperiode == .
replace direct_elect = 1 if wahlperiode !=.

*Drop data aggregated at state-/county-level and duplicates
gen code2 = mod(code,1000)
drop if code2 == 0 & code != 411000 & code !=412000 & code !=611000 & code !=413000 & code !=414000
duplicates drop code year, force

*create variable that states year of first direct election
gen first = year if first_direlect == 1

*IMPOSE PANEL DIMENSION ON DATASET
tsset code year, yearly

*interpolate missing values for controls
replace bevoelkerung = (L.bevoelkerung+F.bevoelkerung)/2 if bevoelkerung ==.
replace bis14jahre = (L.bis14jahre+F.bis14jahre)/2 if bis14jahre ==.
replace ueber65 = (L.ueber65+F.ueber65)/2 if ueber65 ==.
replace gewerbesteuerhebe = (L.gewerbesteuerhebe+F.gewerbesteuerhebe)/2 if gewerbesteuerhebe ==.
replace grundsteuerhebe_b = (L.grundsteuerhebe_b+F.grundsteuerhebe_b)/2 if grundsteuerhebe_b ==.

*DEFINE PER CAPITA GRANTS
gen Invzuweis_pc = Ei_ZuInL/bevoelkerung
gen Schluess_pc = Ei_SlzuL/bevoelkerung
gen log_Invzuweis_pc=log(Invzuweis_pc+1)
gen log_Schluess_pc = log(Schluess_pc+1)

*DEFINE POPULATION SHARES AND TOTAL POPULATION IN THOUSANDS
gen bis14jahre_sh=100*(bis14jahre/bevoelkerung)
gen ueber65_sh=100*(ueber65/bevoelkerung)
replace bevoelkerung=bevoelkerung/1000

*COPY DOWN INFORMATION ON FIRST ELECTIONS 
by code: replace first = first[_n-1] if first == .
order first wahltag
by code: replace first = first[_n+1] if first == .
by code: replace first = first[_n+1] if first == .
by code: replace first = first[_n+1] if first == .
by code: replace first = first[_n+1] if first == .
by code: replace first = first[_n+1] if first == .
by code: replace first = first[_n+1] if first == .
by code: replace first = first[_n+1] if first == .
by code: replace first = first[_n+1] if first == .
by code: replace first = first[_n+1] if first == .
by code: replace first = first[_n+1] if first == .
by code: replace first = first[_n+1] if first == .
by code: replace first = first[_n+1] if first == .
by code: replace first = first[_n+1] if first == .
by code: replace first = first[_n+1] if first == .
by code: replace first = first[_n+1] if first == .
by code: replace first = first[_n+1] if first == .
sort code year

*----------------------------------------------------------------------
*ADD COLLECTED INFORMATION ON MAYORS WHO WERE IN OFFICE IN BOTH SYSTEMS
*----------------------------------------------------------------------
*BORKEN* // "Heࠬer, Bernd"
generate incbothrules = 1 if code == 634001 & year >= 1988 & year <= 2010

*BENSHEIM* // "Stolle, Georg"
replace incbothrules = 1 if code == 431002 & year >= 1972 & year <=2002

*GUDENSBERG* // "Dinges, Paul"
replace incbothrules = 1 if code == 634007 & year >= 1976 & year <= 1999

*OTTRAU* // "Keil, Georg"
replace incbothrules = 1 if code == 634020 & year >= 1988 & year <= 2005

*ALSFELD* // "Diestelmann, Herbert"
replace incbothrules = 1 if code == 535001 & year >= 1988 & year <= 2007

*LAUTERBACH* // "Falk, Otto"
replace incbothrules = 1 if code == 535011 & year >= 1988 & year <= 1996

*HEPPENHEIM* // "Obermayr, Ulrich"
replace incbothrules = 1 if code == 431011 & year >= 1988 & year <= 2005

*VIERNHEIM* // "Hofmann, Norbert" 
replace incbothrules = 1 if code == 431020 & year >= 1988 & year <= 1997

*WALD-MICHELBACH* // "Dietrich, Karl-Heinz"
replace incbothrules = 1 if code == 431021 & year >= 1982 & year <= 1999

*LINSENGERICHT* // "Ratzka, Theodor" 
replace incbothrules = 1 if code == 435018 & year >= 1988 & year <= 2002

*MENGERSKIRCHEN* // "Becker, Robert"
replace incbothrules = 1 if code == 533011 & year >= 1982 & year <= 2005

*DIEBURG* // "Christ, Peter" 
replace incbothrules = 1 if code == 432004 & year >= 1988 & year <= 2005

*FISCHBACHTAL* // "Vierheller, Ludwig" 
replace incbothrules = 1 if code == 432007 & year >= 1988 & year <= 2005

*BAD KARLSHAFEN* // "Wehmeier, Hans Christian"
replace incbothrules = 1 if code == 633002 & year >= 1975 & year <= 1999

*BAUNATAL* // "Grenacher, Heinz"
replace incbothrules = 1 if code == 633003 & year >= 1982 & year <= 2005

*IMMENHAUSEN* // "Deichmann, Adolf"
replace incbothrules = 1 if code == 633014 & year >= 1982 & year <= 1999

*WOLFHAGEN* // "Dietrich, Giselher"
replace incbothrules = 1 if code == 633028 & year >= 1982 & year <= 1999

*BATTENBERG* // "Klein, Lutz"
replace incbothrules = 1 if code == 635004 & year >= 1975 & year <= 1996

*GEMݎDEN* // "Vollm򬬥r, Rainer-Hans" 
replace incbothrules = 1 if code == 635012 & year >= 1988 & year <= 1996

*WEIࠅNBORN* // "M嶲er, Arno"
replace incbothrules = 1 if code == 636015 & year >= 1972 & year <= 2002

*BUSECK* // "K𨮬 Walter" 
replace incbothrules = 1 if code == 531003 & year >= 1988 & year <= 1997

*DAUTPHETAL* // "Hauswirth, Hans"
replace incbothrules = 1 if code == 534007 & year >= 1974 & year <= 2004

*DREIEICH* // "Abeln, Bernd" 
replace incbothrules = 1 if code == 438002 & year >= 1988 & year <= 2001

*DIEMELSEE* // "Fischer, Hans-J𲧥n"
replace incbothrules = 1 if code == 635007 & year >= 1982 & year <= 2005

*FRŎKISCH-CRUMBACH* // "Loos, Philipp"
replace incbothrules = 1 if code == 437007 & year >= 1969 & year <= 1999

*ROMROD* // "Marek, Rudolf" 
replace incbothrules = 1 if code == 535014 & year >= 1988 & year <= 2004

*GROSSUMSTADT* // "K򢬥r, Wilfried" 
replace incbothrules = 1 if code == 432010 & year >= 1989 & year <= 2005

*BAD ZWESTEN* // "Haupt, Heinrich"
replace incbothrules = 1 if code == 634027 & year >= 1976 & year <= 1999

*ALLENDORF* // "Amend, Robert"
replace incbothrules = 1 if code == 635001 & year >= 1970 & year <= 1999

*GORXHEIMERTAL* // "Fitz, Germaid"
replace incbothrules = 1 if code == 431008 & year >= 1983 & year <= 1999

*GRAEVENWIESBACH* // "Herber, Hellwig" 
replace incbothrules = 1 if code == 434004 & year >= 1988 & year <= 2010

*HATTERSHEIM AM MAIN* // "Schubert, Alfred"
replace incbothrules = 1 if code == 436005 & year >= 1982 & year <= 1998

*ROTHENBERG* // "Menges, Willi" 
replace incbothrules = 1 if code == 437014 & year >= 1988 & year <= 1999

*RAUNHEIM* // "Haas, Herbert" 
replace incbothrules = 1 if code == 433010 & year >= 1988 & year <= 1999

*BIEBERTAL* // "Leicht, G𮴥r" 
replace incbothrules = 1 if code == 531002 & year >= 1988 & year <= 1999

*RONSHAUSEN* // "Zilch, Friedhold" 
replace incbothrules = 1 if code == 632017 & year >= 1988 & year <= 2010

*NIEDERDORFELDEN* // "Schneider, Wilfried"
replace incbothrules = 1 if code == 435022 & year >= 1982 & year <= 2006

*B݄INGEN* // "Bauner, Eberhard"
replace incbothrules = 1 if code == 440004 & year >= 1982 & year <= 1998

*ELZ* // "Schumacher, Winfried"
replace incbothrules = 1 if code == 533006 & year >= 1982 & year <= 2005

*STADTALLENDORF* // "Vollmer, Manfred"
replace incbothrules = 1 if code == 534018 & year >= 1982 & year <= 2012

*WITZENHAUSEN* // "Engel, G𮴥r" 
replace incbothrules = 1 if code == 636016 & year >= 1988 & year <= 2005

*RABENAU* // "Eckl, Wilfried" 
replace incbothrules = 1 if code == 531015 & year >= 1988 & year <= 2005

*STAUFENBERG* // "M𮣨, Horst" 
replace incbothrules = 1 if code == 531017 & year >= 1988 & year <= 2010

*LOHRA* // "Brand, Hermann"
replace incbothrules = 1 if code == 534013 & year >= 1976 & year <= 2005

*SCHOTTEN* // "Zimmermann, Hans Otto" 
replace incbothrules = 1 if code == 535016 & year >= 1988 & year <= 2005

*MUECKE* // "B򣫬 Hans-Dieter" 
replace incbothrules = 1 if code == 535013 & year >= 1988 & year <= 1999

*FULDABRUECK* // "M𬬥r, Wilhelm"
replace incbothrules = 1 if code == 633008 & year >= 1982 & year <= 2005

*TRENDELBURG* // "Klug, Bernhard" 
replace incbothrules = 1 if code == 633025 & year >= 1989 & year <= 2010

*SCHENKLENGSFELD* // "Hannich, Horst"
replace incbothrules = 1 if code == 632019 & year >= 1975 & year <= 2005

*FLIEDEN* // "Ebert, Ludwig"
replace incbothrules = 1 if code == 631008 & year >= 1976 & year <= 2000

*GROSSALMERODE* // "Kistner, Wolfgang" 
replace incbothrules = 1 if code == 636004 & year >= 1989 & year <= 2000

*HESSISCH LICHTENAU* // "Winter, Adolf"
replace incbothrules = 1 if code == 636006 & year >= 1989 & year <= 2000

*SCHWALBACH TAUNUS* // "Faeser, Horst" 
replace incbothrules = 1 if code == 436011 & year >= 1989 & year <= 2002

*WETTER* // "Rincke, Dieter" 
replace incbothrules = 1 if code == 534021 & year >= 1988 & year <= 2006

*BAD EMSTAL* // "Br嶴igam, Eckhard" 
replace incbothrules = 1 if code == 633006 & year >= 1989 & year <= 2006

*FLORSTADT* // "Trupp, Heinrich"
replace incbothrules = 1 if code == 440007 & year >= 1983 & year <= 2000

*SONTRA* // "B𣨬ing, Gerhard" 
replace incbothrules = 1 if code == 636011 & year >= 1989 & year <= 2002

*LAUTERTAL* // "S򤬥r, Norbert"
replace incbothrules = 1 if code == 535012 & year >= 1983 & year <= 2006

*BAD SODEN-SALMݎSTER* // "D򲩮g, Bruno"
replace incbothrules = 1 if code == 435002 & year >= 1976 & year <= 2000

*OBERAULA* // "Vestweber, Karl-Heinz"
replace incbothrules = 1 if code == 634019 & year >= 1993 & year <= 2004

*ORTENBERG* // "Emrich, Otto"
replace incbothrules = 1 if code == 440019 & year >= 1989 & year <= 2000

*EGELSBACH* // "Eyࠥn, Heinz" 
replace incbothrules = 1 if code == 438003 & year >= 1989 & year <= 2000

*LAHNAU* // "Jung, Dieter"
replace incbothrules = 1 if code == 532015 & year >= 1979 & year <= 1999

*FRIEDEWALD* // "Sch姥r, Werner"
replace incbothrules = 1 if code == 632006 & year >= 1977 & year <= 2000

*FULDA* // "Dr. Hamberger, Wolfgang"
replace incbothrules = 1 if code == 631009 & year >= 1970 & year <= 1998

*KALBACH* // "Kaib, Karl-Heinz"
replace incbothrules = 1 if code == 631016 & year >= 1983 & year <= 2006

*FREIGERICHT* // "Franz, Manfred W." 
replace incbothrules = 1 if code == 435009 & year >= 1989 & year <= 2006

*RODENBACH* // "Seikel, Karlheinz"
replace incbothrules = 1 if code == 435023 & year >= 1976 & year <= 1996

*LUETZELBACH* // "Old, Werner"
replace incbothrules = 1 if code == 437010 & year >= 1983 & year <= 2005

*GILSERBERG* // "Thiel, Ernst" 
replace incbothrules = 1 if code == 634006 & year >= 1989 & year <= 2000

*SCHRECKSBACH* // "Diehl, Erich"
replace incbothrules = 1 if code == 634021 & year >= 1989 & year <= 2010

*SCHWARZENBORN* // "M򬬥r, Karl-Heinz"
replace incbothrules = 1 if code == 634023 & year >= 1983 & year <= 2000

*NENTERSHAUSEN* // "Schmidt, Lothar"
replace incbothrules = 1 if code == 632013 & year >= 1983 & year <= 2006

*ROࠄORF* // "Jakoubek, Alfred"
replace incbothrules = 1 if code == 432020 & year >= 1983 & year <= 1997

*FULDATAL* // "Stoepel, Rudolf"
replace incbothrules = 1 if code == 633009 & year >= 1983 & year <= 2000

*KIRTORF* // "K𮺬 Ulrich"
replace incbothrules = 1 if code == 535010 & year >= 1977 & year <= 2010

*KORBACH* // "Bonhage, Wolfgang F."
replace incbothrules = 1 if code == 635015 & year >= 1977 & year <= 2000

*OTZBERG* // "M𬬥r, Reinhard" 
replace incbothrules = 1 if code == 432017 & year >= 1989 & year <= 2000

*BREIDENBACH* // "K𮫥l, Artur"
replace incbothrules = 1 if code == 534005 & year >= 1973 & year <= 2000

*GEMUENDEN (FELDA)* // "Antmansky, Peter" 
replace incbothrules = 1 if code == 535005 & year >= 1989 & year <= 2000

*WEHRETAL* // "Dietzel, Horst" 
replace incbothrules = 1 if code == 636014 & year >= 1990 & year <= 2006

*FERNWALD* // "Howe, Dieter"
replace incbothrules = 1 if code == 531004 & year >= 1972 & year <= 2003

*LEUN* // "Straࠨeim, Karl Heinz"
replace incbothrules = 1 if code == 532016 & year >= 1972 & year <= 2000

*WOHRATAL* // "Hamatschek, Klaus"
replace incbothrules = 1 if code == 534022 & year >= 1973 & year <= 2000

*HASSELROTH* // "Traxel, Klaus" 
replace incbothrules = 1 if code == 435015 & year >= 1989 & year <= 2006

*GELNHAUSEN* // "Michaelis, J𲧥n"
replace incbothrules = 1 if code == 435010 & year >= 1977 & year <= 2006

*WALDECK* // "Brandenburg, Peter"
replace incbothrules = 1 if code == 635021 & year >= 1983 & year <= 2006

*HݔTENBERG* // "Schmidt, Hans"
replace incbothrules = 1 if code == 532014 & year >= 1964 & year <= 1999

*EDERTAL* // "Schreiber, Willi-Ernst" 
replace incbothrules = 1 if code == 635009 & year >= 1989 & year <= 2000

*HATZFELD* // "Ermisch, Uwe" 
replace incbothrules = 1 if code == 635014 & year >= 1989 & year <= 2010

*TWISTETAL* // "Hartmann, G𮴨er" 
replace incbothrules = 1 if code == 635018 & year >= 1989 & year <= 2010

*EPPSTEIN* // "Hofmann, Richard"
replace incbothrules = 1 if code == 436002 & year >= 1968 & year <= 2000

*HUENSTETTEN* // "Schumann, Helmut"
replace incbothrules = 1 if code == 439007 & year >= 1973 & year <= 2000

*LINDEN* // "Dr. Lenz, Ulrich"
replace incbothrules = 1 if code == 531012 & year >= 1977

*BUETTELBORN* //"G򬺥nleuchter, Horst"
replace incbothrules = 1 if code == 433003 & year >= 1983

*DIETZENBACH* // "Heyer, J𲧥n" 
replace incbothrules = 1 if code == 438001 & year >= 1990 & year <= 2001

*NEUSTADT* // "Hoim, Manfred" 
replace incbothrules = 1 if code == 534016 & year >= 1990 & year <= 2007

*NIESTE* // "Holzhauer,  Heinrich"
replace incbothrules = 1 if code == 633019 & year >= 1984 & year <= 2000

*TANN (RHOEN)* // "Herchenhan, Dieter" 
replace incbothrules = 1 if code == 631023 & year >= 1990 & year <= 2001

*SCH׎ECK* // "Schmidt, Erwin"
replace incbothrules = 1 if code == 435026 & year >= 1970 & year <= 2001

*BݒSTADT* // "Haag, Alfons" 
replace incbothrules = 1 if code == 431005 & year >= 1990 & year <= 2010

*AARBERGEN* // "Schrader, Wolf"
replace incbothrules = 1 if code == 439001 & year >= 1971 & year <= 2001

*OBERWESER* // "Henne, Roland"
replace incbothrules = 1 if code == 633021 & year >= 1971 & year <= 2001

*WAHLSBURG* // "Heusner, Horst"
replace incbothrules = 1 if code == 633027 & year >= 1971 & year <= 2001

*ALSBACH-HňNLEIN* // "Wennrich, Reinhard" 
replace incbothrules = 1 if code == 432001 & year >= 1990 & year <= 2007

*LINDENFELS* // "Woitge, Peter"
replace incbothrules = 1 if code == 431015 & year >= 1984 & year <= 2001

*NIEDERNHAUSEN* // "Döring, Günter F." 
replace incbothrules = 1 if code == 439011 & year >= 1990 & year <= 2010

*GROSS-GERAU* // "Hohl, Manfred"
replace incbothrules = 1 if code == 433006 & year >= 1984 & year <= 2001

*MOERFELDEN-WALLDORF* // "Brehl, Bernhard"
replace incbothrules = 1 if code == 433008 & year >= 1976 & year <= 2007

*KN݌LWALD* // "M𬬥r, J򲧢 
replace incbothrules = 1 if code == 634011 & year >= 1990 & year <= 2010

*HERBORN* // "Sonnhoff, Bernd"
replace incbothrules = 1 if code == 532012 & year >= 1977 & year <= 2001

*ZWINGENBERG* // "Knapp, Kurt"
replace incbothrules = 1 if code == 431022 & year >= 1984 & year <= 2001

*KIEDRICH* // "Tide, Hans Karl"
replace incbothrules = 1 if code == 439009 & year >= 1990 & year <= 2005

*HOFHEIM AM TAUNUS* // "Felix, Rolf" 
replace incbothrules = 1 if code == 436007 & year >= 1990 & year <= 2001

*GREBENHAIN* // "Dickert, Manfred"
replace incbothrules = 1 if code == 535007 & year >= 1984

*POPPENHAUSEN* // "Wehner, Wolfgang"
replace incbothrules = 1 if code == 631021 & year >= 1984 & year <= 2001

*EHRENBERG (RHOEN)* // "Schreiner, Thomas"
replace incbothrules = 1 if code == 631005 & year >= 1973

*BRACHTTAL* // "G򬺬 Werner" 
replace incbothrules = 1 if code == 435005 & year >= 1990 & year <=2001

*NAUMBURG* // "Matzath, J𲧥n" 
replace incbothrules = 1 if code == 633018 & year >= 1990 & year <=2007

*V׈L* // "Pl𮮥cke, Harald"
replace incbothrules = 1 if code == 635019 & year >= 1990

*WANFRIED* // "Frank, Otto" 
replace incbothrules = 1 if code == 636013 & year >= 1990 & year <=2007

*HEIDENROD* // "Flach, Friedrich"
replace incbothrules = 1 if code == 439005 & year >= 1984 & year <=2001

*BREUNA* // "Henkelmann, Klaus-Dieter" 
replace incbothrules = 1 if code == 633004 & year >= 1990 & year <=2010

*PHILIPPSTHAL (WERRA)* // "Sch姥r, Fritz"
replace incbothrules = 1 if code == 632016 & year >= 1977 & year <=2001

*PFUNGSTADT* // "Baier, Horst" 
replace incbothrules = 1 if code == 432018 & year >= 1990 & year <=2010

*BROMBACHTAL* // "Kredel, Bernd"
replace incbothrules = 1 if code == 437005 & year >= 1978 & year <=2004

*BREUBERG* // "Verst, G𮴥r"
replace incbothrules = 1 if code == 437004 & year >= 1984 & year <=2007

*SENSBACHTAL* // "Heiss, Manfred" 
replace incbothrules = 1 if code == 437015 & year >= 1990 & year <=2007

*REISKIRCHEN* // "D򲩮g, Klaus"
replace incbothrules = 1 if code == 531016 & year >= 1984 & year <=2001

*BREITSCHEID* // "Dechert, Siegfried"
replace incbothrules = 1 if code == 532004 & year >= 1978 & year <=2001

*FRANKENAU* // "Peterka, Reiner" 
replace incbothrules = 1 if code == 635010 & year >= 1990 & year <=2001

*NEUHOF* // "Hohmann, Martin"
replace incbothrules = 1 if code == 631018 & year >= 1984 & year <=1998

*BAD SCHWALBACH* // "Janisch, G𮴥r" 
replace incbothrules = 1 if code == 439002 & year >= 1990 & year <=2001

*IDSTEIN* // "M𬬥r, Hermann"
replace incbothrules = 1 if code == 439008 & year >= 1978 & year <=2001

*TAUNUSSTEIN* // "Hofmann, Gerhard" 
replace incbothrules = 1 if code == 439015 & year >= 1990 & year <=2001

*ESCHBORN* // "Herkstr򴥲, Martin" 
replace incbothrules = 1 if code == 436003 & year >= 1990 & year <=2001

*KEFENROD* // "Kling, Bernd" 
replace incbothrules = 1 if code == 440013 & year >= 1990 & year <=2010

*ERLENSEE* // "Heller, Manfred" 
replace incbothrules = 1 if code == 435007 & year >= 1990 & year <=2001

*LICH* // "Seiboldt, Ludwig"
replace incbothrules = 1 if code == 531011 & year >= 1984 & year <=2007

*LORCH* // "Retzmann, G𮴥r" 
replace incbothrules = 1 if code == 439010 & year >= 1990 & year <=2007

*ANTRIFTTAL* // "Averdung, Johannes" 
replace incbothrules = 1 if code == 535002 & year >= 1990 & year <=2010

*LICHTENFELS* // "Wennemuth, Wolfgang"
replace incbothrules = 1 if code == 635016 & year >= 1972 & year <=2001

*HAMMERSBACH* // "Meininger, Helga"
replace incbothrules = 1 if code == 435013 & year >= 1985 & year <=2004

*ROCKENBERG* // "Bingel, Patrick" 
replace incbothrules = 1 if code == 440022 & year >= 1990 & year <=2004

*DILLENBURG* // "Meckel, Helmut" 
replace incbothrules = 1 if code == 532006 & year >= 1990 & year <=2001

*FELSBERG* // "Stiegel, Klaus"
replace incbothrules = 1 if code == 634003 & year >= 1984 & year <=2007

*SCHOEFFENGRUND* // "Rech, Stefan"
replace incbothrules = 1 if code == 532018 & year >= 1984 & year <=2001

*WŃHTERSBACH* // "Kr嵳chmer, Karl Rainer" 
replace incbothrules = 1 if code == 435029 & year >= 1991 & year <=2010

*FLOERSBACHTAL* // "Sakschewski, Horst" 
replace incbothrules = 1 if code == 435008 & year >= 1990 & year <=2006

*HݎFELD* // "Dr. Fennel, Eberhard"
replace incbothrules = 1 if code == 631015 & year >= 1978

*GREBENAU* // "Ackermann, J𲧥n" 
replace incbothrules = 1 if code == 535006 & year >= 1991 & year <=2010

*GROSSBIEBERAU* // "Seubert, Werner"
replace incbothrules = 1 if code == 432009 & year >= 1985 & year <=2008

*NIDDATAL* // "Martin, Wilfried"
replace incbothrules = 1 if code == 440017 & year >= 1985 & year <=2002

*HOMBERG (EFZE)* // "Blau, Helmut" 
replace incbothrules = 1 if code == 634009 & year >= 1991 & year <=2002

*HAIGER* // "Dr. Zoubek, Gerhard" 
replace incbothrules = 1 if code == 532011 & year >= 1991 & year <=2010

*MEINHARD* // "Schott, Hubert"
replace incbothrules = 1 if code == 636007 & year >= 1985 & year <=2002

*LANGEN* // "Pitthan, Dieter" 
replace incbothrules = 1 if code == 438006 & year >= 1991 & year <=2008

*ESPENAU* // "Bunzenthal, Ehrhard" 
replace incbothrules = 1 if code == 633007 & year >= 1991 & year <=2002

*GROSSROHRHEIM* // "Roos, Heinz" 
replace incbothrules = 1 if code == 431010 & year >= 1991 & year <=2008

*GRݎDAU* // "Meyer, Georg"
replace incbothrules = 1 if code == 435012 & year >= 1985 & year <=2007

*BEERFELDEN* // "Engelter, Adolf"
replace incbothrules = 1 if code == 437002 & year >= 1985 & year <=2000

*STEINAU A.D.STR.* // "Knobeloch, Hans-Joachim" 
replace incbothrules = 1 if code == 435028 & year >= 1991 & year <=2002

*WALDBRUNN (WESTERWALD)* // "Bl嵴el, Lothar" 
replace incbothrules = 1 if code == 533016 & year >= 1991 & year <=2010

*WALDKAPPEL* // "Hillebrandt, Peter"
replace incbothrules = 1 if code == 636012 & year >= 1985 & year <=2008

*BREITENBACH A.H.* // "Orth, Paul" 
replace incbothrules = 1 if code == 632004 & year >= 1991 & year <=2002

*FRANKENBERG (EDER)* // "Eichenlaub, Helmut"
replace incbothrules = 1 if code == 635011 & year >= 1985 & year <=1998

*BABENHAUSEN* // "Lambert, Kurt" 
replace incbothrules = 1 if code == 432002 & year >= 1991 & year <=2002

*BAD HOMBURG v.d HOEHE* // "Assmann, Wolfgang" 
replace incbothrules = 1 if code == 434001 & year >= 1981 & year <=1998

*GLADENBACH* // "Bartnik, Klaus" 
replace incbothrules = 1 if code == 534010 & year >= 1991 & year <=2002

*SIEGBACH* // "Dietrich, Erich"
replace incbothrules = 1 if code == 532019 & year >= 1978 & year <=1998

*HIRSCHHORN (NECKAR)* // "D򲲬 Ilona" 
replace incbothrules = 1 if code == 431012 & year >= 1991 & year <=1999

*NEUKIRCHEN* // "Olbrich, Klemens" 
replace incbothrules = 1 if code == 634017 & year >= 1991 & year <=2010

*REINHARDSHAGEN* // "Merkwirth, Lothar"
replace incbothrules = 1 if code == 633022 & year >= 1985 & year <=2008

*KRONBERG IM TAUNUS* // "Kre߬ Wilhelm" 
replace incbothrules = 1 if code == 434006 & year >= 1991 & year <=2008

*HOCHHEIM AM MAIN* // "Schindler, Harald" 
replace incbothrules = 1 if code == 436006 & year >= 1991 & year <=2002

*LIMESHAIN* // "H𨮬 Klaus" 
replace incbothrules = 1 if code == 440014 & year >= 1991 & year <=2002

*VELLMAR* // "St𣫲ath, Kurt"
replace incbothrules = 1 if code == 633026 & year >= 1985 & year <=2002

*BURGHAUN* // "Braun, Wolfgang" 
replace incbothrules = 1 if code == 631002 & year >= 1991 & year <=2002

*REICHELSHEIM (Odw.)* // "Lode, Gerd" 
replace incbothrules = 1 if code == 437013 & year >= 1991 & year <=2008

*FREIENSTEINAU* // "Kopp, Friedel" 
replace incbothrules = 1 if code == 535004 & year >= 1991 & year <=2010

*KגLE* // "Ochs, Fritz" 
replace incbothrules = 1 if code == 634012 & year >= 1982 & year <=1999

*HELSA* // "Schmidt, Uwe" 
replace incbothrules = 1 if code == 633012 & year >= 1991 & year <=2002

*RONNEBURG* // "Kleine, Friedhelm"
replace incbothrules = 1 if code == 435024 & year >= 1973 & year <=2002

*REICHELSHEIM (Wett)* // "Wagner, Gerd" 
replace incbothrules = 1 if code == 440021 & year >= 1986 & year <=2008

*MגLENBACH* // "Knopf, Lothar" 
replace incbothrules = 1 if code == 431017 & year >= 1991 & year <=2010

*NIEDERAULA* // "Opfer, Helmut" 
replace incbothrules = 1 if code == 632015 & year >= 1991 & year <=2008

*EPPERTSHAUSEN* // "Weber, Herbert" 
replace incbothrules = 1 if code == 432005 & year >= 1991 & year <=2002

*LIEDERBACH a/Ts.* // "Lehner, Gerhard"
replace incbothrules = 1 if code == 436010 & year >= 1979 & year <=2008

*HIRZENHAIN* // "Pfannkuche, Elfriede" 
replace incbothrules = 1 if code == 440011 & year >= 1991 & year <=2008

*HERBSTEIN* // "Pfeil, Manfred" 
replace incbothrules = 1 if code == 535008 & year >= 1991 & year <=2000

*ULRICHSTEIN* // "Horst, Erwin Heinrich"
replace incbothrules = 1 if code == 535018 & year >= 1985 & year <=2011

*HABICHTSWALD* // "Aࠨauer, Wolfgang" 
replace incbothrules = 1 if code == 633011 & year >= 1991 & year <=2008

*ZIERENBERG* // "Pf𴺥, J𲧥n" 
replace incbothrules = 1 if code == 633029 & year >= 1991 & year <=2008

*MEIࠎER* // "Fritz, Adolf"
replace incbothrules = 1 if code == 636008 & year >= 1979 & year <=2002

*FLOERSHEIM AM MAIN* // "Wolf, Dieter"
replace incbothrules = 1 if code == 436004 & year >= 1979 & year <=2001

*WARTENBERG* // "Dickel, Manfred" 
replace incbothrules = 1 if code == 535019 & year >= 1991 & year <=2010

*FELDATAL* // "Schrimpf, Robert" 
replace incbothrules = 1 if code == 535003 & year >= 1991 & year <=1998

*STOCKSTADT a/RHEIN* // "Horst, Klaus" 
replace incbothrules = 1 if code == 433013 & year >= 1991 & year <=2008

*KOENIGSTEIN i/Ts.* //"Huke, Bertram"
replace incbothrules = 1 if code == 434005 & year >= 1992 & year <=2000

*GROࠅNL݄ER* // "Will, Helmut" 
replace incbothrules = 1 if code == 631011 & year >= 1991 & year <=2002

*HILDERS* // "Kl𢥲, Lothar" 
replace incbothrules = 1 if code == 631012 & year >= 1991 & year <=2002

*ELBTAL* // "Lenz, Hubert" 
replace incbothrules = 1 if code == 533005 & year >= 1991 & year <=2010

*HOFBIEBER* // "Lauer, Eberhardt" 
replace incbothrules = 1 if code == 631013 & year >= 1991 & year <=2002

*BAD SALZSCHLIRF* // "Stender, Ernst-August" 
replace incbothrules = 1 if code == 631001 & year >= 1991 & year <=2002

*NEUBERG* // "Hofmann, Uwe"
replace incbothrules = 1 if code == 435020 & year >= 1979 & year <=2002

*MODAUTAL* // "Speckhardt, Wilhelm" 
replace incbothrules = 1 if code == 432013 & year >= 1992 & year <=2000

*LAUBACH* // "Spandau, Claus" 
replace incbothrules = 1 if code == 531010 & year >= 1991 & year <=2008

*BURGWALD* // "Daume, Adam" 
replace incbothrules = 1 if code == 635006 & year >= 1992 & year <=2008

*ESCHWEGE* // "Zick, J𲧥n" 
replace incbothrules = 1 if code == 636003 & year >= 1986 & year <=2009

*HADAMAR* // "Beresko, Hans" 
replace incbothrules = 1 if code == 533007 & year >= 1992 & year <=2009

*KIRCHHEIM* // "Spangenberg, Karl-Heinz" 
replace incbothrules = 1 if code == 632011 & year >= 1986 & year <=1997

*MICHELSTADT* // "Ruhr, Reinhold"
replace incbothrules = 1 if code == 437011 & year >= 1980 & year <=2009

*NIEDENSTEIN* // "Lange, Werner" 
replace incbothrules = 1 if code == 634018 & year >= 1992 & year <=2010

*REINHEIM* // "Hartmann, Karl"
replace incbothrules = 1 if code == 432019 & year >= 1986

*WABERN* // "Jung, G𮴥r" 
replace incbothrules = 1 if code == 634025 & year >= 1992 & year <=2010

*BIEBERGEMUEND* // "Dickert, Thomas" 
replace incbothrules = 1 if code == 435003 & year >= 1992 & year <=2009

*SELIGENSTADT* // "Wenzel, Rolf" 
replace incbothrules = 1 if code == 438013 & year >= 1991 & year <=2003

*RANSTADT* // "Landmann, Erhard" 
replace incbothrules = 1 if code == 440020 & year >= 1992 & year <=2009

*LIMBURG a.d.LAHN* // "Richard, Martin" 
replace incbothrules = 1 if code == 533009 & year >= 1998 & year <=2010

*WEHRHEIM* // "Michel, Helmut" 
replace incbothrules = 1 if code == 434012 & year >= 1992 & year <=2001

*GIESSEN* // "Mutz, Manfred"
replace incbothrules = 1 if code == 531005 & year >= 1986 & year <=2003

*GRݎBERG* // "Damaschke, Siegbert"
replace incbothrules = 1 if code == 531006 & year >= 1980 & year <=2003

*WETTENBERG* // "Schmidt, Gerhard"
replace incbothrules = 1 if code == 531018 & year >= 1986 & year <=2009

*BAD SODEN A. TAUNUS* // "Bender, Kurt E." 
replace incbothrules = 1 if code == 436001 & year >= 1992 & year <=2003

*KUENZELL* // "Br𣫬 Hermann"
replace incbothrules = 1 if code == 631017 & year >= 1986 & year <=2003

*NEUENSTEIN* // "Schmidt, Walter"
replace incbothrules = 1 if code == 632014 & year >= 1980 & year <=2003

*MAINHAUSEN* // "Gr򮩮g, Dieter" 
replace incbothrules = 1 if code == 438007 & year >= 1987 & year <=2003

*EHRINGSHAUSEN* // "Niebich, Eberhard" 
replace incbothrules = 1 if code == 532008 & year >= 1992 & year <=2003

*OBER-MOERLEN* // "Sch姥r, Erika" 
replace incbothrules = 1 if code == 440018 & year >= 1992 & year <=2000

*MUEHLHEIM AM MAIN* // "Schelzke, Karl-Christian" 
replace incbothrules = 1 if code == 438008 & year >= 1992 & year <=1999

*KARBEN* // "Engel, Detlev" 
replace incbothrules = 1 if code == 440012 & year >= 1992 & year <=2003

*GLAUBURG* // "Langlitz, Eberhard" 
replace incbothrules = 1 if code == 440010 & year >= 1991 & year <=2003

*HAUNECK* // "Hebig, Ernst" 
replace incbothrules = 1 if code == 632007 & year >= 1992 & year <=2004

*SULZBACH (TAUNUS)* // "Uhrig, Herbert"
replace incbothrules = 1 if code == 436012 & year >= 1986 & year <=2003

*WILDECK* // *"M𬬥r, Willi"
replace incbothrules = 1 if code == 632020 & year >= 1979 & year <=1997

*MERENBERG* // *"Born, Gerald" 
replace incbothrules = 1 if code == 533012 & year >= 1993 & year <=2003

*SPANGENBERG* // *"K򢢥rling, Hans-J𲧥n" 
replace incbothrules = 1 if code == 634024 & year >= 1992 & year <=2003

*COELBE* // *"Butte, J𲧥n"
replace incbothrules = 1 if code == 534006 & year >= 1979 & year <=2001

*BIRKENAU* // *"Kanz, Albert" 
replace incbothrules = 1 if code == 431004 & year >= 1992 & year <=2003

*GROSSKROTZENBURG* // *"Reuter, Klaus" 
replace incbothrules = 1 if code == 435011 & year >= 1993 & year <=2004

*KAUFUNGEN* // *"Burghardt, G𮴥r" 
replace incbothrules = 1 if code == 633015 & year >= 1993 & year <=2004

*LOHFELDEN* // *"Blank, Bernhard" 
replace incbothrules = 1 if code == 633017 & year >= 1993 & year <=2004

*OBERTSHAUSEN* // *"Seib, Josef" 
replace incbothrules = 1 if code == 438010 & year >= 1993 & year <=2002

*BAD VILBEL* // *"Biwer, G𮴨er"
replace incbothrules = 1 if code == 440003 & year >= 1980 & year <=2003

*GINSH.-GUSTAVSBURG* // *"von Neumann, Richard" 
replace incbothrules = 1 if code == 433005 & year >= 1993 & year <=2010

*BISCHOFSHEIM* // *"D򟬠Berthold"
replace incbothrules = 1 if code == 433002 & year >= 1987 & year <=1998

*SELTERS (TAUNUS)* // *"Zabel, Norbert"
replace incbothrules = 1 if code == 533014 & year >= 1987 & year <=2010

*HERINGEN (WERRA)* // *"Pfromm, Rolf" 
replace incbothrules = 1 if code == 632009 & year >= 1993 & year <=2004

*WILLINGEN (UPLAND)* // *"Bechstein, Hubert" 
replace incbothrules = 1 if code == 635022 & year >= 1993 & year <=2004

*SOLMS* // *"Ludwig, J򲧢 
replace incbothrules = 1 if code == 532021 & year >= 1993 & year <=2010

*SÖHREWALD* // *"Bachmann, Horst" 
replace incbothrules = 1 if code == 633024 & year >= 1993 & year <=2004

*HOHENAHR* // *"Netz, Wilhelm"
replace incbothrules = 1 if code == 532013 & year >= 1987 & year <=2004

*MOSSAUTAL* // *"Keil, Willi"
replace incbothrules = 1 if code == 437012 & year >= 1987

*KIRCHHAIN* // *"Hesse, Klaus" 
replace incbothrules = 1 if code == 534011 & year >= 1993 & year <=2004

*OBER-RAMSTADT* // *"Hartmann, Bernd"
replace incbothrules = 1 if code == 432016 & year >= 1987 & year <=2004

*GEISENHEIM* // *"Federhen, Manfred" 
replace incbothrules = 1 if code == 439004 & year >= 1993 & year <=2010

*SCHL݃HTERN* // *"Fritzsch, Falko" 
replace incbothrules = 1 if code == 435025 & year >= 1993 & year <=2010

*GREBENSTEIN* // *"Kölling, Armin" 
replace incbothrules = 1 if code == 633010 & year >= 1993 & year <=2010

*FRONHAUSEN* // *"Preiss, Helmut"
replace incbothrules = 1 if code == 534009 & year >= 1980 & year <=2003

*EBSDORFERGRUND* // *"Schulz, Andreas" 
replace incbothrules = 1 if code == 534008 & year >= 1993 & year <=2010

*BRECHEN* // *"Königstein, Bernhard"
replace incbothrules = 1 if code == 533002 & year >= 1980 & year <=2004

*BAD CAMBERG* // *"Reitz, Gerhard" 
replace incbothrules = 1 if code == 533003 & year >= 1993 & year <=2004

*BROMSKIRCHEN* // *"Frese, Karl-Friedrich" 
replace incbothrules = 1 if code == 635005 & year >= 1993 & year <=2010

*BIEDENKOPF* // *"Bolldorf, Karl Hermann" 
replace incbothrules = 1 if code == 534004 & year >= 1993 & year <=2010

*ESCHENBURG* // *"Jank, Walter" 
replace incbothrules = 1 if code == 532009 & year >= 1993 & year <=2004

*LOLLAR* // *"Bocks, Gerd"
replace incbothrules = 1 if code == 531013 & year >= 1987 & year <=2004

*HEUSENSTAMM* // *"Eckstein, Josef"
replace incbothrules = 1 if code == 438005 & year >= 1987 & year <=2003

*HAINBURG* // *"Bessel, Bernhard" 
replace incbothrules = 1 if code == 438004 & year >= 1993 & year <=2010

*LAHNTAL* // *"Apell, Manfred" 
replace incbothrules = 1 if code == 534012 & year >= 1993 & year <=2010

*GRIESHEIM* // *"Leber, Norbert"
replace incbothrules = 1 if code == 432008 & year >= 1987

*WEIMAR* // *"Krantz, Karl"
replace incbothrules = 1 if code == 534020 & year >= 1974 & year <=2004

*MELSUNGEN* // *"Dietzel, Karl-Heinz" 
replace incbothrules = 1 if code == 634014 & year >= 1993 & year <=2004


*------------------------------------------------------------------
*MERGE with collected data on mayor appointments
*------------------------------------------------------------------
merge m:m code year using "$data\collected.dta"
drop _merge 

*------------------------------------------------------------------
*MERGE with collected data (confidential) on mayor characteristics
*------------------------------------------------------------------
*merge m:m code year using "$data\mayorcharact"
*drop _merge

*fix dates for elections (day and month mixed up)
gen wahltag_new = wrong_eledate
replace wahltag_new=wahltag if wahlregel=="D"
drop wrong_eledate
drop if year>2010

*correct potential merging problems
drop if Reg_Gem ==.

drop geschl landkreisgemeinde koerperschaft gewaehlt traeger_gewaehlt traeger_gewaehlt_temp
sort Name year

*SPLITTING UP OF ELECTION DATES IN MONTH, DAY, YEAR
split wahltag_new if wahlregel =="D",g(part) p("/")
rename part1 monat_wahl
rename part2 tag_wahl
rename part3 jahr_wahl

split wahltag_new if wahlregel =="I",g(part) p(".")
rename part1 tag_wahl_I
rename part2 monat_wahl_I
rename part3 jahr_wahl_I

replace monat_wahl = monat_wahl_I if monat_wahl ==""
replace jahr_wahl = jahr_wahl_I if jahr_wahl ==""
destring monat_wahl, gen(month_elect)
drop tag_wahl tag_wahl_I monat_wahl_I jahr_wahl_I
sort code year

*create variables that indicate election/appointment years, etc.
replace bmwahl=0 if bmwahl==.

gen pre_bmwahl=f.bmwahl
replace pre_bmwahl = 1 if pre_bmwahl[_n-6] ==1 & pre_bmwahl ==.
replace pre_bmwahl = 0 if pre_bmwahl ==.
 
gen bmappoint = 1 if wahlregel == "I"
replace bmappoint = 0 if bmappoint ==.

gen any_system = max(bmwahl, bmappoint)
by code: gen pre_bmappoint = bmappoint[_n+1]
replace pre_bmappoint = 0 if pre_bmappoint ==.

gen new_pre_bmappoint = max(bmappoint, pre_bmappoint)
gen new_pre_bmwahl = max(bmwahl, pre_bmwahl)
gen pre_any_system = max(new_pre_bmwahl, new_pre_bmappoint)

gen appointment=0
replace appointment=1 if any_system==1 & direct_elect==0

*general data cleaning/definitions
gen logbev = log(bevoelkerung)
duplicates drop year Reg_Gem, force
sort code year

gen electyr = 0
replace electyr = 1 if appointment == 1
replace electyr = 1 if bmwahl == 1

drop appointment bmappoint pre_bmappoint any_system new_pre_bmappoint new_pre_bmwahl 

*------------------------------------------------------------------
*MERGE with data on seat distribution in local councils
*------------------------------------------------------------------
merge m:m code year using "$data\councilseats.dta"
drop _merge

* drop five irregular cities
drop if code ==  411000 | code ==412000 | code ==611000 | code ==413000 | code ==414000
sort code year

* fill in missing years
foreach c of varlist total_seats - auf_seats {
	replace `c'=l.`c' if `c'==.
	}

* generate seat shares 
foreach c of varlist cdu_seats - auf_seats {
	gen `c'_sh=(`c'/total_seats)*100
	}
	
* absolute majorites
gen cdu_fdp_majority=(cdu_seats_sh+fdp_seats_sh>50)
gen spd_green_majority=(spd_seats_sh+greenparty_seats_sh>50)

* replace the missing obs inn the traeger new temp variable
sort code year
gen traeger_new_temp_old2=traeger_new_temp
bysort code: replace traeger_new_temp=traeger_new_temp[_n-1] if traeger_new_temp==""

gen left_mayor=regexm(traeger_new_temp, "SPD|GRÜNE|Grüne")
gen right_mayor=regexm(traeger_new_temp, "CDU|FDP")
gen independent_mayor=(left_mayor==0 & right_mayor==0 )

gen independent_seat_sh=100-cdu_seats_sh-fdp_seats_sh-spd_seats_sh -greenparty_seats_sh
gen independent_majority=(independent_seat_sh>50)

drop traeger_new_temp_old2 *_seat* 

*Rename variables
rename traeger_new_temp mayor_party
rename gewerbesteuerhebe businesstax
rename grundsteuerhebe_b proptax
rename logbev logpop

sort code year

*------------------------------------------------------------------
*MERGE with data on municipal expenditures
*------------------------------------------------------------------
merge m:m code year using "$data\expenditures"
drop if _merge!=3
drop _merge

* define other fiscal variables
gen totrev=Ei_Verw + Ei_Verm
gen totrev_pc=(Ei_Verw + Ei_Verm)/(bevoelkerung*1000)
gen totexp_pc=Au_Insg/(bevoelkerung*1000)
gen logtotrev_pc=log(totrev_pc)
gen logtotexp_pc=log(totexp_pc)

*Year dummies
tab year, gen(time)

* complete dummy variable that indicates mayors under both systems (with at least 6 yrs in office)
sort code year
encode gewaehlt_new, gen(temp)
replace temp=l.temp if l.temp!=. & temp==.
replace incbothrules=. if temp!=l.temp & incbothrules==1 
bysort code: egen temp2=count(incbothrules)  if year>=first
bysort code: egen temp3=count(incbothrules)  if year<first
replace temp3 = l.temp3 if temp3 ==.
xtivreg2 log_Invzuweis_pc direct_elect bmwahl electyr time* if incbothrules == 1 & temp3 >= 6, fe r cluster(code)

* create indicator for subset of mayor-year observations with mayors that were in office under both regimes
gen ins = 1 if e(sample)
by code: replace ins = 1 if temp3 >=6 & incbothrules[_n+1] == 1 & incbothrules[_n-1] == 1
by code: replace ins = 1 if ins[_n-1] == 1 & wahlregel =="D"

*Create county-specific time trends
nsplit code, d(3) g(lk)
levelsof lk1, local(lkreis)
foreach c of local lkreis{
gen trend_`c'=0
bysort code (year): replace trend_`c'=_n if lk1==`c'
}

drop typ lk1 lk2 stichwahl Ei_* Au_Insg Reg_Gem totexp_pc totrev_pc totrev koerperschaft jahr_wahl monat_wahl wahltag code2 bis14jahre ueber65 code3dig traeger_new

save "$data\final", replace
